--    Program: 		CAPPOIMP_PURCHASE_OPTION.sql
--    Description:  Import PO PURCHASE OPTION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_structure_id  number;
                    		                 		
   cursor cur_po_system_parameters_all is
      select * from CRST_PO_SYSTEM_PARAMETERS_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_system_parameters_all loop   
   	 l_err_msg_tmp := '';
   	 
   	 l_position_structure_id := null;
   	 if cur.SECURITY_POSITION_STRUCTURE_ID is not null then
   	   begin
   	   	 select position_structure_id_n 
   	   	 into   l_position_structure_id
         from   crst_per_position_structures
         where  position_structure_id = cur.SECURITY_POSITION_STRUCTURE_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Position Hierarchy Structure '||cur.SECURITY_POSITION_STRUCTURE_ID||' not found'; 	
	   end;	   	
     end if;
     
     begin
       -- import data
       insert into PO_SYSTEM_PARAMETERS_ALL
       (
          LAST_UPDATE_DATE                ,
          LAST_UPDATED_BY                 ,
          LAST_UPDATE_LOGIN               ,
          CREATION_DATE                   ,
          CREATED_BY                      ,
          ACCRUED_CODE_COMBINATION_ID     ,
          PRICE_CHANGE_ALLOWANCE          ,
          REQ_CAN_AUTHORIZE               ,
          INSPECTION_REQUIRED_FLAG        ,
          ENFORCE_PRICE_CHANGE_ALLOWANCE  ,
          DEFAULT_RATE_TYPE               ,
          TAXABLE_FLAG                    ,
          USER_DEFINED_RECEIPT_NUM_CODE   ,
          MANUAL_RECEIPT_NUM_TYPE         ,
          USER_DEFINED_QUOTE_NUM_CODE     ,
          MANUAL_QUOTE_NUM_TYPE           ,
          USER_DEFINED_RFQ_NUM_CODE       ,
          MANUAL_RFQ_NUM_TYPE             ,
          USER_DEFINED_PO_NUM_CODE        ,
          MANUAL_PO_NUM_TYPE              ,
          USER_DEFINED_REQ_NUM_CODE       ,
          MANUAL_REQ_NUM_TYPE             ,
          DEFAULT_QUOTE_WARNING_DELAY     ,
          ENFORCE_BUYER_AUTHORITY_FLAG    ,
          ENFORCE_BUYER_NAME_FLAG         ,
          ENFORCE_VENDOR_HOLD_FLAG        ,
          NOTIFY_IF_BLANKET_FLAG          ,
          RFQ_REQUIRED_FLAG               ,
          PO_DOCUMENT_REPORT_ID           ,
          PO_DOCUMENT_PRINT_FLAG          ,
          RFQ_DOCUMENT_REPORT_ID          ,
          RFQ_DOCUMENT_PRINT_FLAG         ,
          LINE_TYPE_ID                    ,
          NEXT_APPROVER_LOOKUP_CODE       ,
          RECEIVING_FLAG                  ,
          ALLOW_ITEM_DESC_UPDATE_FLAG     ,
          DISPOSITION_WARNING_FLAG        ,
          ENFORCE_FULL_LOT_QUANTITIES     ,
          SECURITY_POSITION_STRUCTURE_ID  ,
          MIN_RELEASE_AMOUNT              ,
          PRICE_TYPE_LOOKUP_CODE          ,
          NOTIFICATION_WARNING_DELAY      ,
          PRICE_BREAK_LOOKUP_CODE         ,
          INVOICE_CLOSE_CODE              ,
          RECEIVE_CLOSE_CODE              ,
          INVOICE_CLOSE_TOLERANCE         ,
          RECEIVE_CLOSE_TOLERANCE         ,
          CANCEL_REQS_ON_PO_CANCEL_FLAG   ,
          REQUEST_ID                      ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          REQIMPORT_GROUP_BY_CODE         ,
          EXPENSE_ACCRUAL_CODE            ,
          INVENTORY_ACCRUAL_CODE          ,
          ORDER_TYPE_ID                   ,
          ORDER_SOURCE_ID                 ,
          SALESREP_ID                     ,
          TERM_ID                         ,
          ORG_ID                          ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE20              ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          TAX_FROM_SHIP_TO_LOC_FLAG       ,
          TAX_FROM_ITEM_FLAG              ,
          TAX_FROM_VENDOR_FLAG            ,
          TAX_FROM_VENDOR_SITE_FLAG       ,
          TAX_FROM_SYSTEM_FLAG            ,
          TAX_HIER_SHIP_TO_LOC            ,
          TAX_HIER_ITEM                   ,
          TAX_HIER_VENDOR                 ,
          TAX_HIER_VENDOR_SITE            ,
          TAX_HIER_SYSTEM                 ,
          CREATE_DEBIT_MEMO_FLAG          ,
          ENFORCE_PRICE_CHANGE_AMOUNT     ,
          PRICE_CHANGE_AMOUNT              
       )
       values
       (
            cur.LAST_UPDATE_DATE                ,
            CUR.LAST_UPDATED_BY,
            cur.LAST_UPDATE_LOGIN               ,
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCRUED_CODE_COMBINATION_ID),
            cur.PRICE_CHANGE_ALLOWANCE          ,
            cur.REQ_CAN_AUTHORIZE               ,
            cur.INSPECTION_REQUIRED_FLAG        ,
            cur.ENFORCE_PRICE_CHANGE_ALLOWANCE  ,
            cur.DEFAULT_RATE_TYPE               ,
            cur.TAXABLE_FLAG                    ,
            cur.USER_DEFINED_RECEIPT_NUM_CODE   ,
            cur.MANUAL_RECEIPT_NUM_TYPE         ,
            cur.USER_DEFINED_QUOTE_NUM_CODE     ,
            cur.MANUAL_QUOTE_NUM_TYPE           ,
            cur.USER_DEFINED_RFQ_NUM_CODE       ,
            cur.MANUAL_RFQ_NUM_TYPE             ,
            cur.USER_DEFINED_PO_NUM_CODE        ,
            cur.MANUAL_PO_NUM_TYPE              ,
            cur.USER_DEFINED_REQ_NUM_CODE       ,
            cur.MANUAL_REQ_NUM_TYPE             ,
            cur.DEFAULT_QUOTE_WARNING_DELAY     ,
            cur.ENFORCE_BUYER_AUTHORITY_FLAG    ,
            cur.ENFORCE_BUYER_NAME_FLAG         ,
            cur.ENFORCE_VENDOR_HOLD_FLAG        ,
            cur.NOTIFY_IF_BLANKET_FLAG          ,
            cur.RFQ_REQUIRED_FLAG               ,
            cur.PO_DOCUMENT_REPORT_ID           ,
            cur.PO_DOCUMENT_PRINT_FLAG          ,
            cur.RFQ_DOCUMENT_REPORT_ID          ,
            cur.RFQ_DOCUMENT_PRINT_FLAG         ,
            cur.LINE_TYPE_ID                    ,
            cur.NEXT_APPROVER_LOOKUP_CODE       ,
            cur.RECEIVING_FLAG                  ,
            cur.ALLOW_ITEM_DESC_UPDATE_FLAG     ,
            cur.DISPOSITION_WARNING_FLAG        ,
            cur.ENFORCE_FULL_LOT_QUANTITIES     ,
            l_position_structure_id             ,
            cur.MIN_RELEASE_AMOUNT              ,
            cur.PRICE_TYPE_LOOKUP_CODE          ,
            cur.NOTIFICATION_WARNING_DELAY      ,
            cur.PRICE_BREAK_LOOKUP_CODE         ,
            cur.INVOICE_CLOSE_CODE              ,
            cur.RECEIVE_CLOSE_CODE              ,
            cur.INVOICE_CLOSE_TOLERANCE         ,
            cur.RECEIVE_CLOSE_TOLERANCE         ,
            cur.CANCEL_REQS_ON_PO_CANCEL_FLAG   ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.REQIMPORT_GROUP_BY_CODE         ,
            cur.EXPENSE_ACCRUAL_CODE            ,
            cur.INVENTORY_ACCRUAL_CODE          ,
            cur.ORDER_TYPE_ID                   ,
            cur.ORDER_SOURCE_ID                 ,
            cur.SALESREP_ID                     ,
            cur.TERM_ID                         ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.TAX_FROM_SHIP_TO_LOC_FLAG       ,
            cur.TAX_FROM_ITEM_FLAG              ,
            cur.TAX_FROM_VENDOR_FLAG            ,
            cur.TAX_FROM_VENDOR_SITE_FLAG       ,
            cur.TAX_FROM_SYSTEM_FLAG            ,
            cur.TAX_HIER_SHIP_TO_LOC            ,
            cur.TAX_HIER_ITEM                   ,
            cur.TAX_HIER_VENDOR                 ,
            cur.TAX_HIER_VENDOR_SITE            ,
            cur.TAX_HIER_SYSTEM                 ,
            cur.CREATE_DEBIT_MEMO_FLAG          ,
            cur.ENFORCE_PRICE_CHANGE_AMOUNT     ,
            cur.PRICE_CHANGE_AMOUNT             
       );   
	 exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
